﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.Common;
using System.Configuration;
namespace CodeGeneraterUseT4.TTFiles.sql_helper_see.test
{
    class sample
    {
    }

    public class tblRGF_KeywordDefinition
    {
        public string RGRPTID { get; set; }

        public short RGSEQNO { get; set; }

        public string RGKEYWORD { get; set; }

        public string RGTYPE { get; set; }

        public short? RGLEN { get; set; }

        public bool? RGDEC { get; set; }

        public string RGSHORT { get; set; }

        public string RGLONG1 { get; set; }

        public string RGLONG2 { get; set; }

        public string RGLONG3 { get; set; }

        public string RGEXPR1 { get; set; }

        public string RGEXPR2 { get; set; }

        public string RGEXPR3 { get; set; }

        public string RGEXPR4 { get; set; }

        public string RGEXPR5 { get; set; }

        public string RGEXPR6 { get; set; }

        public string RGINIT { get; set; }

        public string RGINITBEF { get; set; }

        public bool? RGINITBLVL { get; set; }

        public string RGWITH { get; set; }

        public string RGRESET { get; set; }

        public bool? RGBREAK { get; set; }

        public string RGGROUP1 { get; set; }

        public string RGGROUP2 { get; set; }

        public string RGGROUP3 { get; set; }

        public string RGGROUP4 { get; set; }

        public string RGGROUP5 { get; set; }

        public string RGGROUP6 { get; set; }

        public string RGWHEN1 { get; set; }

        public string RGWHEN2 { get; set; }

        public string RGWHEN3 { get; set; }

        public string RGWHEN4 { get; set; }

        public string RGWHEN5 { get; set; }

        public string RGWHEN6 { get; set; }

        public string FILLER { get; set; }

    }

    public class tblRGF_KeywordDefinitionDao
    {
        private const string RGRPTIDColumn = "RGRPTID";
        private const string RGSEQNOColumn = "RGSEQNO";
        private const string RGKEYWORDColumn = "RGKEYWORD";
        private const string RGTYPEColumn = "RGTYPE";
        private const string RGLENColumn = "RGLEN";
        private const string RGDECColumn = "RGDEC";
        private const string RGSHORTColumn = "RGSHORT";
        private const string RGLONG1Column = "RGLONG1";
        private const string RGLONG2Column = "RGLONG2";
        private const string RGLONG3Column = "RGLONG3";
        private const string RGEXPR1Column = "RGEXPR1";
        private const string RGEXPR2Column = "RGEXPR2";
        private const string RGEXPR3Column = "RGEXPR3";
        private const string RGEXPR4Column = "RGEXPR4";
        private const string RGEXPR5Column = "RGEXPR5";
        private const string RGEXPR6Column = "RGEXPR6";
        private const string RGINITColumn = "RGINIT";
        private const string RGINITBEFColumn = "RGINITBEF";
        private const string RGINITBLVLColumn = "RGINITBLVL";
        private const string RGWITHColumn = "RGWITH";
        private const string RGRESETColumn = "RGRESET";
        private const string RGBREAKColumn = "RGBREAK";
        private const string RGGROUP1Column = "RGGROUP1";
        private const string RGGROUP2Column = "RGGROUP2";
        private const string RGGROUP3Column = "RGGROUP3";
        private const string RGGROUP4Column = "RGGROUP4";
        private const string RGGROUP5Column = "RGGROUP5";
        private const string RGGROUP6Column = "RGGROUP6";
        private const string RGWHEN1Column = "RGWHEN1";
        private const string RGWHEN2Column = "RGWHEN2";
        private const string RGWHEN3Column = "RGWHEN3";
        private const string RGWHEN4Column = "RGWHEN4";
        private const string RGWHEN5Column = "RGWHEN5";
        private const string RGWHEN6Column = "RGWHEN6";
        private const string FILLERColumn = "FILLER";
        private static readonly string[] ColumnCollections = new[] { RGRPTIDColumn, RGSEQNOColumn, RGKEYWORDColumn, RGTYPEColumn, RGLENColumn, RGDECColumn, RGSHORTColumn, RGLONG1Column, RGLONG2Column, RGLONG3Column, RGEXPR1Column, RGEXPR2Column, RGEXPR3Column, RGEXPR4Column, RGEXPR5Column, RGEXPR6Column, RGINITColumn, RGINITBEFColumn, RGINITBLVLColumn, RGWITHColumn, RGRESETColumn, RGBREAKColumn, RGGROUP1Column, RGGROUP2Column, RGGROUP3Column, RGGROUP4Column, RGGROUP5Column, RGGROUP6Column, RGWHEN1Column, RGWHEN2Column, RGWHEN3Column, RGWHEN4Column, RGWHEN5Column, RGWHEN6Column, FILLERColumn };
        private readonly string _columnCollectionSelectString = string.Join(",", ColumnCollections);
        private  static   string[] KeyColumns = new string[] { "RGRPTID", "RGSEQNO" };
        private     string WhereClause = KeyColumns.JoinFormat(" AND ", "{0} =@{0}");
        //private readonly Database _database;
        DbHelper dbDal=null;
        public tblRGF_KeywordDefinitionDao()
        {
           // _database = DatabaseFactory.CreateDatabase("DataGeneratorTable");
            dbDal=new DbHelper();
        }

        public tblRGF_KeywordDefinitionDao(DbHelper database)
        {
           // _database = database;
        }

        private string GetSortText(SortDefinitions sortDefinitions)
        {
            string sortDefinition;
            if (sortDefinitions != null && sortDefinitions.SoftItems.Count > 0)
            {
                sortDefinition = string.Join(" , ", sortDefinitions.SoftItems.Select(p => string.Format("{0} {1}", p.ColumnName, (p.SortOrder == SortOrder.None) ? "" : p.SortOrder.ToString())));

                sortDefinition = string.Format(" ORDER BY {0}", sortDefinition);
            }
            else
            {
                sortDefinition = "ORDER BY " + string.Join(",", KeyColumns);
            }
            return sortDefinition;
        }

        public List<tblRGF_KeywordDefinition> GetPage(SortDefinitions sortDefinitions, int startIndex, int pageSize)
        {
            string sqlText;
            var sortText = GetSortText(sortDefinitions);
            if (startIndex > -1 && pageSize > 0)
            {
                sqlText = GetPagedQuery(sortText, startIndex, pageSize);
            }
            else
            {
                sqlText = GettSelectSQL();
            }
            var command = dbDal.GetSqlStringCommond(sqlText);
            DataTable dt = dbDal.ExecuteDataTable(command);
            return FromDt(dt);
        }

        private tblRGF_KeywordDefinition From(DataRow dr)
        {
            tblRGF_KeywordDefinition tmp = new tblRGF_KeywordDefinition();
            return tmp;
        }

        private List<tblRGF_KeywordDefinition> FromDt(DataTable dt)
        {
            List<tblRGF_KeywordDefinition> list = new List<tblRGF_KeywordDefinition>();
            foreach (DataRow dr in dt.Rows)
            {
                list.Add(From(dr));
            }
            return list;
        }

        public List<tblRGF_KeywordDefinition> GetAll()
        {
            var command = dbDal.GetSqlStringCommond(GettSelectSQL());
            DataTable dt = dbDal.ExecuteDataTable(command);
            return FromDt(dt);            
        }

        public tblRGF_KeywordDefinition GetByKey(tblRGF_KeywordDefinition tblrgf_keyworddefinition)
        {
            tblRGF_KeywordDefinition tml = new tblRGF_KeywordDefinition();

            var command = dbDal.GetSqlStringCommond(GetSelectSQLWithKey());
            AddKeyColumns(command, tblrgf_keyworddefinition.RGRPTID, tblrgf_keyworddefinition.RGSEQNO);
            DataTable dt = dbDal.ExecuteDataTable(command);
             List<tblRGF_KeywordDefinition> list=FromDt(dt);
             if (list.Count > 0)
             {
                 tml = list[0];
             }
           
             return tml;  
        }

        private string GetSelectSQLWithKey()
        {
            var sql = string.Format(@"SELECT  {0}
                            FROM 
                                tblRGF_KeywordDefinitions
                            WHERE
                                {1}
                                       ", _columnCollectionSelectString, WhereClause);
            return sql;
        }

        //public class tblRGF_KeywordDefinitionSelectKeyParameterMapper : IParameterMapper
        //{
        //    public void AssignParameters(DbCommand command, object[] parameterValues)
        //    {
        //        for (int i = 0; i < KeyColumns.Count(); i++)
        //        {
        //            var parameter = command.CreateParameter();
        //            parameter.ParameterName = string.Format("@{0}", KeyColumns[i]);
        //            parameter.Value = parameterValues[i];
        //            command.Parameters.Add(parameter);
        //        }


        //    }
        //}
        public long Insert(tblRGF_KeywordDefinition tblrgf_keyworddefinition)
        {
            var command = dbDal.GetSqlStringCommond(InsertSQL());
            AddInsertParameters(command, tblrgf_keyworddefinition);
            dbDal.ExecuteNonQuery(command);
            return 1;
        }

        public void Update(tblRGF_KeywordDefinition tblrgf_keyworddefinition,DbTransaction tran)
        {
            var command = dbDal.GetSqlStringCommond(UpdatetSQL());
            AddUpdateParameters(command, tblrgf_keyworddefinition);
            command.Transaction = tran;
            dbDal.ExecuteNonQuery(command);
        }

        public void Delete(tblRGF_KeywordDefinition tblrgf_keyworddefinition)
        {
            var command = dbDal.GetSqlStringCommond(DeletetSQL());
            AddKeyColumns(command, tblrgf_keyworddefinition.RGRPTID, tblrgf_keyworddefinition.RGSEQNO);
            dbDal.ExecuteNonQuery(command);
        }
        private void AddKeyColumns(DbCommand command, params  object[] paramValues)
        {
            for (int i = 0; i < KeyColumns.Count(); i++)
            {
                DbParameter parameter = command.CreateParameter();
                parameter.ParameterName = string.Format(dbDal.ParameterSign+"{0}", KeyColumns[i]);
                parameter.Value = paramValues[i];
                command.Parameters.Add(parameter);
            }

        }

        private void AddInsertParameters(DbCommand command, tblRGF_KeywordDefinition tblrgf_keyworddefinition)
        {
            DbParameter parameter = null;
            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format(dbDal.ParameterSign+"{0}", @RGRPTIDColumn);
            parameter.Value = tblrgf_keyworddefinition.RGRPTID;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGSEQNOColumn);
            parameter.Value = tblrgf_keyworddefinition.RGSEQNO;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGKEYWORDColumn);
            parameter.Value = (object)tblrgf_keyworddefinition.RGKEYWORD ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGTYPEColumn);
            parameter.Value = (object)tblrgf_keyworddefinition.RGTYPE ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGLENColumn);
            parameter.Value = (object)tblrgf_keyworddefinition.RGLEN ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGDECColumn);
            parameter.Value = (object)tblrgf_keyworddefinition.RGDEC ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGSHORTColumn);
            parameter.Value = (object)tblrgf_keyworddefinition.RGSHORT ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGLONG1Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGLONG1 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGLONG2Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGLONG2 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGLONG3Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGLONG3 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGEXPR1Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGEXPR1 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGEXPR2Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGEXPR2 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGEXPR3Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGEXPR3 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGEXPR4Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGEXPR4 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGEXPR5Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGEXPR5 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGEXPR6Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGEXPR6 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGINITColumn);
            parameter.Value = (object)tblrgf_keyworddefinition.RGINIT ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGINITBEFColumn);
            parameter.Value = (object)tblrgf_keyworddefinition.RGINITBEF ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGINITBLVLColumn);
            parameter.Value = (object)tblrgf_keyworddefinition.RGINITBLVL ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGWITHColumn);
            parameter.Value = (object)tblrgf_keyworddefinition.RGWITH ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGRESETColumn);
            parameter.Value = (object)tblrgf_keyworddefinition.RGRESET ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGBREAKColumn);
            parameter.Value = (object)tblrgf_keyworddefinition.RGBREAK ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGGROUP1Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGGROUP1 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGGROUP2Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGGROUP2 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGGROUP3Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGGROUP3 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGGROUP4Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGGROUP4 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGGROUP5Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGGROUP5 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGGROUP6Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGGROUP6 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGWHEN1Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGWHEN1 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGWHEN2Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGWHEN2 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGWHEN3Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGWHEN3 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGWHEN4Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGWHEN4 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGWHEN5Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGWHEN5 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @RGWHEN6Column);
            parameter.Value = (object)tblrgf_keyworddefinition.RGWHEN6 ?? DBNull.Value;
            command.Parameters.Add(parameter);

            parameter = command.CreateParameter();
            parameter.ParameterName = string.Format("@{0}", @FILLERColumn);
            parameter.Value = (object)tblrgf_keyworddefinition.FILLER ?? DBNull.Value;
            command.Parameters.Add(parameter);


        }

        private void AddUpdateParameters(DbCommand command, tblRGF_KeywordDefinition tblrgf_keyworddefinition)
        {
           


            AddInsertParameters(command, tblrgf_keyworddefinition);
            int dd;
            object mm;
           dd= mm==null? ?:Convert.
           
        }

        private string GettSelectSQL()
        {
            var sql = string.Format(@" SELECT  {0} 
                                          FROM 
                                                tblRGF_KeywordDefinitions
                                       ", _columnCollectionSelectString);
            return sql;
        }

        private string GetPagedQuery(string sqlText, int startIndex, int pageSize)
        {
            var endIndex = startIndex + pageSize;
            startIndex = startIndex + 1;

            var sql = string.Format(@"SELECT TOP {0} {4} 
						FROM ( 
							SELECT    {4} , ROW_NUMBER() OVER ({1}) AS ROW 
						    FROM  tblRGF_KeywordDefinitions ) AS WithRowNumber
					    WHERE
                            ROW BETWEEN {2} AND {3} {1}", pageSize, sqlText, startIndex, endIndex, _columnCollectionSelectString);

            return sql;
        }

        private string InsertSQL()
        {
            var nonIdentColumns = ColumnCollections;
            var sql = string.Format(@" INSERT INTO
                            tblRGF_KeywordDefinitions (
                              {0}
                            )
                            VALUES
                            (
                                {1}
                            ) ;
                                       ", nonIdentColumns.JoinFormat(",", "{0}"), nonIdentColumns.JoinFormat(",", dbDal.ParameterSign+"{0}"));
            return sql;
        }

        private string UpdatetSQL()
        {
            var sql = string.Format(@" UPDATE
                                 tblRGF_KeywordDefinitions
                            SET
                               {1}
                            WHERE 
                                  {0}
                                       ", WhereClause, ColumnCollections.Except(KeyColumns).ToArray().JoinFormat(" , ", "{0} = @{0}"));
            return sql;
        }

        private string DeletetSQL()
        {
            var sql = string.Format(@" DELETE  FROM
                              tblRGF_KeywordDefinitions
                            WHERE 
                                {0}           
                                       ", WhereClause);
            return sql;
        }

    }
    public static class ExtensionMethods
    {
        public static string JoinFormat(this string[] values, string separator, string format)
        {
            if (values == null)
                throw new ArgumentNullException("values");
            if (values.Length == 0 || values[0] == null)
                return string.Empty;
            if (separator == null)
                separator = string.Empty;
            var stringBuilder = new StringBuilder();
            string str1 = values[0];
            if (str1 != null)
                stringBuilder.AppendFormat(format, str1);
            for (int index = 1; index < values.Length; ++index)
            {
                stringBuilder.Append(separator);
                if (values[index] != null)
                {
                    string str2 = values[index];
                    if (str2 != null)
                        stringBuilder.AppendFormat(format, str2);
                }
            }
            return stringBuilder.ToString();
        }

        public static string JoinFormat<T>(this IEnumerable<T> values, string separator, string format)
        {
            if (values == null)
                throw new ArgumentNullException("values");
            var enumerator = values.GetEnumerator();
            var stringBuilder = new StringBuilder();

            if (enumerator.MoveNext() == false || enumerator.Current == null)
            {
                return string.Empty;

            }
            stringBuilder.AppendFormat(format, enumerator.Current);
            while (enumerator.MoveNext())
            {
                stringBuilder.Append(separator);
                if (enumerator.Current != null)
                {
                    stringBuilder.AppendFormat(format, enumerator.Current);
                }
            }

            return stringBuilder.ToString();
        }

        public static string JoinFormat<T>(this IEnumerable<T> values, string separator, Func<T, string> action)
        {
            if (values == null)
                throw new ArgumentNullException("values");
            var enumerator = values.GetEnumerator();
            var stringBuilder = new StringBuilder();

            if (enumerator.MoveNext() == false || enumerator.Current == null)
            {
                return string.Empty;

            }
            stringBuilder.Append(action(enumerator.Current));
            while (enumerator.MoveNext())
            {
                stringBuilder.Append(separator);
                if (enumerator.Current != null)
                {
                    stringBuilder.Append(action(enumerator.Current));
                }
            }

            return stringBuilder.ToString();
        }
    }

    public class SortDefinitions
    {
        private readonly List<SortItem> _softItems = new List<SortItem>();
        public List<SortItem> SoftItems
        {
            get { return _softItems; }
        }
    }

    public class SortItem
    {
        public string ColumnName { get; set; }
        public SortOrder SortOrder { get; set; }
    }

    public enum SortOrder
    {
        None
      ,
        ASC
            , DESC
    }

    public class DbHelper
    {
       
        private DbConnection connection;
        private DbProviderFactory dbfactory;
        public DbHelper()
        {
            this.connection = CreateConnection();
            if (ProviderName.ToLower().Contains("sqlclient"))
            {
                ParameterSign = "@";
            }
        }
        public DbHelper(string connectionString)
        {
            this.connection = CreateConnection();
        }
        // oracle :  sqlserver @
        public  string ParameterSign = ":";
        private static string  ProviderName="";

        public static DbConnection CreateConnection()
        {
            ConnectionStringSettings set = ConfigurationManager.ConnectionStrings["DbConn"];
            ProviderName = set.ProviderName;            
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(ProviderName);
            DbConnection dbconn = dbfactory.CreateConnection();
            dbconn.ConnectionString = set.ConnectionString;
            return dbconn;
        }       

        public DbCommand GetStoredProcCommond(string storedProcedure)
        {
            DbCommand dbCommand = connection.CreateCommand();
            dbCommand.CommandText = storedProcedure;
            dbCommand.CommandType = CommandType.StoredProcedure;
            return dbCommand;
        }
        public DbCommand GetSqlStringCommond(string sqlQuery)
        {
            DbCommand dbCommand = connection.CreateCommand();
            dbCommand.CommandText = sqlQuery;
            dbCommand.CommandType = CommandType.Text;
            return dbCommand;
        }

        #region
        //增加参数#region 增加参数
        public void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection)
        {
            foreach (DbParameter dbParameter in dbParameterCollection)
            {
                cmd.Parameters.Add(dbParameter);
            }
        }
        public void AddOutParameter(DbCommand cmd, string parameterName, DbType dbType, int size)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Size = size;
            dbParameter.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(dbParameter);
        }
        public void AddInParameter(DbCommand cmd, string parameterName, DbType dbType, object value)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(dbParameter);
        }
        public void AddReturnParameter(DbCommand cmd, string parameterName, DbType dbType)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(dbParameter);
        }
        public DbParameter GetParameter(DbCommand cmd, string parameterName)
        {
            return cmd.Parameters[parameterName];
        }

        #endregion
        #region
        // 执行 #region执行
        public DataSet ExecuteDataSet(DbCommand cmd)
        {
            
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            dbDataAdapter.Fill(ds);
            return ds;
        }

        public DataTable ExecuteDataTable(DbCommand cmd)
        {           
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataTable dataTable = new DataTable();
            dbDataAdapter.Fill(dataTable);
            return dataTable;
        }

        public DbDataReader ExecuteReader(DbCommand cmd)
        {
            cmd.Connection.Open();
            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return reader;
        }
        public int ExecuteNonQuery(DbCommand cmd)
        {
            cmd.Connection.Open();
            int ret = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            return ret;
        }

        public object ExecuteScalar(DbCommand cmd)
        {
            cmd.Connection.Open();
            object ret = cmd.ExecuteScalar();
            cmd.Connection.Close();
            return ret;
        }
        #endregion
        #region
        // 执行事务#region 执行事务
        public DataSet ExecuteDataSet(DbCommand cmd, Trans t)
        {
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;           
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            dbDataAdapter.Fill(ds);
            return ds;
        }

        public DataTable ExecuteDataTable(DbCommand cmd, Trans t)
        {
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;            
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataTable dataTable = new DataTable();
            dbDataAdapter.Fill(dataTable);
            return dataTable;
        }

        public DbDataReader ExecuteReader(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            DbDataReader reader = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            return reader;
        }
        public int ExecuteNonQuery(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            int ret = cmd.ExecuteNonQuery();
            return ret;
        }

        public object ExecuteScalar(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            object ret = cmd.ExecuteScalar();
            return ret;
        }
        #endregion
    }
}
